package com.ajavaer.framework.office;

import com.ajavaer.framework.common.tools.LoadJsTools;
import com.ajavaer.framework.common.tools.ObjectTools;
import com.ajavaer.framework.common.tools.ReflectTools;
import com.ajavaer.framework.common.tools.StringTools;
import com.ajavaer.framework.office.annotaion.Office;
import com.ajavaer.framework.office.annotaion.OfficeType;
import com.ajavaer.framework.office.excel.annotation.ExcelAnnotaionReader;
import com.ajavaer.framework.office.excel.annotation.ExcelColumn;
import com.ajavaer.framework.office.excel.annotation.ExcelSheet;
import com.ajavaer.framework.office.excel.exception.ExcelAnnotationException;
import com.ajavaer.framework.office.excel.model.CellExpression;
import com.ajavaer.framework.office.excel.model.ExcelColumnModel;
import com.ajavaer.framework.office.excel.model.ExcelSheetModel;
import com.ajavaer.framework.office.files.ExcelFile;
import com.ajavaer.framework.office.logic.ExportLogic;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.script.ScriptException;
import java.io.*;
import java.lang.reflect.Field;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 将Excel类导出到文件
 *
 * <p>
 * 1.Excel类指的添加了类注解@Excel<br>
 * 2.Excel类结构和Excel文档类似，成员变量为@ExcelSheet，即包含多个工作簿<br>
 * 3.@ExcelSheet是工作簿模型，是一个List&lt;T&gt;<br>
 * </p>
 */
public class Excel {
    private static Logger log = LoggerFactory.getLogger(Excel.class);

    public static Workbook openExcel(File file) throws IOException {
        if (file == null) {
            return null;
        }
        String[] fileNameArr = file.getName().split("\\.");
        String ext = fileNameArr[fileNameArr.length - 1];
        OfficeType officeType;
        if (ext.equalsIgnoreCase("xlsx")) {
            officeType = OfficeType.xlsx;
        } else {
            officeType = OfficeType.xls;
        }
        return openExcel(new FileInputStream(file), officeType);
    }

    public static Workbook openExcel(InputStream input, OfficeType officeType) throws IOException {
        Workbook workbook;
        if (officeType == OfficeType.xlsx) {
            workbook = new XSSFWorkbook(input);
        } else {
            workbook = new HSSFWorkbook(new POIFSFileSystem(input));
        }
        return workbook;
    }

    public static Sheet openExcel(File file, int sheetIndex) throws IOException {
        Workbook workbook = openExcel(file);
        if (workbook == null) {
            return null;
        }
        return workbook.getSheetAt(sheetIndex);
    }

    public static String getCellValue(Sheet sheet, int rowAt, int cellAt) {
        Row row = sheet.getRow(rowAt);
        return getCellValue(row, cellAt);
    }

    public static String getCellValue(Row row, int cellAt) {
        Cell cell = row.getCell(cellAt);
        if (cell == null) {
            return null;
        }
        cell.setCellType(CellType.STRING);
        return cell.getStringCellValue();
    }

    public static Integer getCellValueInteger(Row row, int cellAt) {
        Cell cell = row.getCell(cellAt);
        if (cell == null) {
            return null;
        }
        cell.setCellType(CellType.STRING);
        return Integer.valueOf(cell.getStringCellValue());
    }


    public static Integer getCellValueInteger(Sheet sheet, int rowAt, int cellAt) {
        return getCellValueInteger(sheet.getRow(rowAt), cellAt);
    }

    public static <T extends ExcelFile> T readExcel(File file, Class<T> clazz) throws IOException, IllegalAccessException, InstantiationException, NoSuchFieldException {
        Workbook workbook = openExcel(file);
        ExcelAnnotaionReader reader = new ExcelAnnotaionReader(clazz);
        int columnCount;
        int sheetCount = reader.getSheetMap().size();
        if (sheetCount == 0) {
            throw new ExcelAnnotationException("请至少创建一个工作簿（Sheet）,在Excel模型类中添加注解@ExcelSheet创建");
        }
        int sheetIndex = 0;
        T t = clazz.newInstance();
        for (Map.Entry<Class<?>, ExcelSheetModel> entry : reader.getSheetMap().entrySet()) {
            Class<?> clazzItem = entry.getKey();
            ExcelSheetModel sheetModel = entry.getValue();
            Map<Integer, Field> sheetFields = sheetModel.getSheetFields();
            Field field = sheetFields.get(sheetIndex);
            Sheet sheet = workbook.getSheetAt(sheetIndex++);
            // 2.2.1获得列模型
            ExcelColumnModel cm = reader.getColumnMap().get(clazzItem);
            if (cm == null) {
                throw new ExcelAnnotationException("列数为0，在sheet模型类中添加注解@ExcelColumn创建列");
            }
            // 2.2.2取得列数
            columnCount = cm.getModelMap().size();
            //忽略表头
            Iterator<Row> rowsIter = sheet.rowIterator();

            List resultData = new ArrayList<>();
            boolean title = false;
            while (rowsIter.hasNext()) {
                Row row = rowsIter.next();
                if (!title) {
                    title = true;
                    continue;
                }
                Object entity = clazzItem.newInstance();
                for (int j = 0; j < columnCount; j++) {
                    ExcelColumn excelColumn = cm.getIndex(j).values().iterator().next();
                    Cell cell = row.getCell(j);
                    if (cell == null) {
                        continue;
                    }
                    // 取得对应的类型
                    Class<?> filedType = excelColumn.type();
                    // 取得属性名称
                    String filed = cm.getIndex(j).keySet().iterator().next();
                    Object value = null;
                    if (filedType == String.class) {// 字符串
                        cell.setCellType(CellType.STRING);
                        value = cell.getStringCellValue();
                    } else if (filedType == Integer.class) {// 整型
                        cell.setCellType(CellType.NUMERIC);
                        value = (int) cell.getNumericCellValue();
                    } else if (filedType == Double.class
                            || filedType == Float.class) {// 双精度或浮点型
                        cell.setCellType(CellType.NUMERIC);
                        value = cell.getNumericCellValue();
                    } else if (filedType == Date.class) {// 日期
                        if (cell.getCellTypeEnum() == CellType.STRING) {
                            String cellValue = cell.getStringCellValue();
                            try {
                                value = new SimpleDateFormat(StringTools.nvl(excelColumn.format(), "yyyy-MM-dd HH:mm:ss")).parse(cellValue);
                            } catch (ParseException e) {
                            }
                        } else {
                            cell.setCellType(CellType.NUMERIC);
                            value = cell.getDateCellValue();
                        }
                    }
                    if (value != null) {
                        ReflectTools.setValueByName(filed, value, entity);
                    }
                }
                resultData.add(entity);
            }
            ReflectTools.setValueByName(field, resultData, t);
        }
        return t;
    }

    /**
     * 注解形式导出excel文件
     *
     * @param data 数据
     * @param out  输出流
     * @throws NoSuchFieldException
     * @throws IllegalAccessException
     * @throws ExcelAnnotationException
     */
    public static <T> void saveExcel(ExcelFile<T> data, OutputStream out) throws IllegalAccessException, ExcelAnnotationException {
        ExcelAnnotaionReader reader = new ExcelAnnotaionReader(data.getClass());
        // 1.获得文档类型和保存路径
        Office office = reader.getOffice();
        OfficeType officeType = office.type();// 文档类型
        // 1.1根据文档类型生成WorkBook
        Workbook workbook = null;
        if (officeType == null) {
            throw new ExcelAnnotationException("请指定文档类型，如:OfficeType.xls或OfficeType.xlsx");
        }
        if (officeType == OfficeType.xlsx) {
            workbook = new XSSFWorkbook();
        } else {
            workbook = new HSSFWorkbook();
        }
        int columnCount = 0;// 工作簿列数
        // 2.获得Sheet工作簿
        // 2.1取得数量
        int sheetCount = reader.getSheetMap().size();
        if (sheetCount == 0) {
            throw new ExcelAnnotationException("请至少创建一个工作簿（Sheet）,在Excel模型类中添加注解@ExcelSheet创建");
        }
        // 2.2根据工作簿数量和名称生成对应的工作簿
        Map<Integer, Sheet> sheetmap = new HashMap<>();
        for (int i = 0; i < sheetCount; i++) {
            for (Class<?> clazz : reader.getSheetMap().keySet()) {
                Map<String, ExcelSheet> sheetModel = reader.getSheetMap()
                        .get(clazz).getIndex(i);
                if (sheetModel == null) {// 为空时表明遍历的不是当前sheet
                    continue;
                }
                String filedName = sheetModel.keySet().iterator().next();
                ExcelSheet excelSheet = sheetModel.values().iterator()
                        .next();
                Sheet sheet = workbook.createSheet(excelSheet.name());
                sheetmap.put(i, sheet);
                // 2.2.1获得列模型
                ExcelColumnModel cm = reader.getColumnMap().get(clazz);
                if (cm == null) {
                    throw new ExcelAnnotationException("列数为0，在sheet模型类中添加注解@ExcelColumn创建列");
                }
                // 2.2.2取得列数
                columnCount = cm.getModelMap().size();
                // 2.2.3创建表头
                Row titleRow = sheet.createRow(0);
                // 2.2.3.1 在索引0的位置创建单元格（左上端）
                for (int j = 0; j < columnCount; j++) {
                    // 取得对应index的注解
                    ExcelColumn excelColumn = cm.getIndex(j).values()
                            .iterator().next();
                    Cell cell = titleRow.createCell((short) j);
                    // 设定类型为字符串
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue(excelColumn.name());
                }
                // 2.2.4对每一列设值(注意行加1)
                // 2.2.4.1获得sheet中所有行的值
                List<?> rowList = (List<?>) ReflectTools.getValueByName(filedName, data);
                for (int k = 1; k < rowList.size() + 1; k++) {
                    Object o = rowList.get(k - 1);
                    Row row = sheet.createRow(k);
                    for (int j = 0; j < columnCount; j++) {
                        // 取得对应index的注解
                        ExcelColumn excelColumn = cm.getIndex(j).values().iterator().next();
                        Cell cell = row.createCell((short) j);
                        // 取得对应的类型
                        Class<?> filedType = excelColumn.type();
                        // 取得属性名称
                        String filed = cm.getIndex(j).keySet().iterator()
                                .next();
                        Object value = ReflectTools.getValueByName(filed, o);
                        if (filedType == String.class) {// 字符串
                            cell.setCellType(CellType.STRING);
                            if (value == null) {
                                cell.setCellValue("");
                            } else {
                                cell.setCellValue(value.toString());
                            }
                        } else if (filedType == Integer.class) {// 整型
                            cell.setCellType(CellType.NUMERIC);
                            if (value != null) {
                                cell.setCellValue(Integer.valueOf(value.toString()));
                            }
                        } else if (filedType == Double.class
                                || filedType == Float.class) {// 双精度或浮点型
                            if (value != null) {
                                cell.setCellType(CellType.NUMERIC);
                                cell.setCellValue(Double.parseDouble(value.toString()));
                            }
                        } else if (filedType == Date.class) {// 日期
                            if (value != null) {
                                cell.setCellValue(new SimpleDateFormat(StringTools.nvl(excelColumn.format(), "yyyy-MM-dd HH:mm:ss")).format(value));
                            }
                        }
                        if (k == rowList.size()) {
                            sheet.autoSizeColumn(j, true);
                        }
                    }
                }

            }
        }
        outExcel(out, workbook);
    }

    /**
     * 输出Excel文件,可以用在web项目中:导出Excel
     *
     * @param out  输出流
     * @param book 工作簿
     */
    public static void outExcel(OutputStream out, Workbook book) {
        try {
            out = new BufferedOutputStream(out);
            book.write(out);
        } catch (IOException e) {
            log.error(e.getMessage(),e);
        } finally {
            if (out != null) {
                try {
                    out.flush();
                    out.close();
                } catch (IOException e) {
                    log.error(e.getMessage(),e);
                }
            }
        }
    }

    public static File saveExcel(File file, Workbook book) {
        try {
            outExcel(new FileOutputStream(file), book);
        } catch (FileNotFoundException e) {
            log.error(e.getMessage(),e);
        }
        return file;
    }

    static ExportLogic getLogic() throws ScriptException {
        ExportLogic logic = LoadJsTools.runJs("var ExportLogic = Java.type('" + ExportLogic.class.getName() + "');" +
                "new ExportLogic({" +
                "            getValue: function (key, d) {" +
                "                return eval(key)" +
                "            }" +
                "        })");
        return logic;
    }


    /**
     * @param sheet
     * @param startRow 从0开始，表达式行
     * @param data
     * @throws Exception
     */
    public static void exportListWithExpression(Sheet sheet, int startRow, List data) throws Exception {
        check(sheet, data);
        ExportLogic logic = getLogic();
        //读取表达式
        Row row = sheet.getRow(startRow);
        if (row != null) {
            Iterator<Cell> cells = row.cellIterator();
            List<CellExpression> expressions = new ArrayList<>();
            while (cells.hasNext()) {
                Cell cell = cells.next();
                String expression = readCellExpression(cell);
                if (StringTools.isNotBlank(expression) && expression.startsWith("${") && expression.endsWith("}")) {
                    String key = expression.substring(2, expression.length() - 1);//取值
                    expressions.add(new CellExpression(cell.getRowIndex(), cell.getColumnIndex(), key));
                } else {
                    expressions.add(null);
                }
            }
            for (int i = 0; i < data.size(); i++) {
                row = sheet.getRow(startRow + i);
                if (row == null) {
                    row = sheet.createRow(startRow + i);
                }
                Object thisRow = data.get(i);
                for (int j = 0; j < expressions.size(); j++) {
                    CellExpression expr = expressions.get(j);
                    Cell cell = row.getCell(expr.getColumnIndex());
                    if (cell == null) {
                        cell = row.createCell(expr.getColumnIndex());
                    }
                    if (StringTools.isNotBlank(expr.getExpression())) {
                        String value = logic.getValue(expr.getExpression(), thisRow);
                        cell.setCellValue(value);
                    }
                }
            }
        }
    }

    private static String readCellExpression(Cell cell) {
        CellType type = cell.getCellTypeEnum();
        String expression = "";
        switch (type) {
            case BLANK:
            case STRING: {
                expression = cell.getStringCellValue();
                break;
            }
            case NUMERIC: {
                expression = cell.getNumericCellValue() + "";
                break;
            }
        }
        return expression;
    }

    public static void exportObjectWithExpression(Sheet sheet, Object data) throws Exception {
        check(sheet, data);
        ExportLogic logic = getLogic();
        Iterator<Row> rows = sheet.rowIterator();
        while (rows.hasNext()) {
            Row row = rows.next();
            Iterator<Cell> cells = row.cellIterator();
            while (cells.hasNext()) {
                Cell cell = cells.next();
                String expression = readCellExpression(cell);
                if (StringTools.isNotBlank(expression) && expression.startsWith("${") && expression.endsWith("}")) {
                    String key = expression.substring(2, expression.length() - 1);//取值
                    String value = logic.getValue(key, data);
                    cell.setCellValue(value);
                }
            }
        }
    }

    private static void check(Sheet sheet, Object data) {
        if (sheet == null || ObjectTools.isBlank(data)) {
            throw new NullPointerException("工作簿和数据不能为空");
        }
    }


}
